package com.sync.data.ievent.repository;



import com.sync.data.ievent.entity.MtSpeakerSchema;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.List;

/**
 * @author liquanchang
 */
@Repository
public interface MtSpeakerSchemaRepository extends JpaRepository<MtSpeakerSchema, String>, JpaSpecificationExecutor<MtSpeakerSchema> {

    @Query(value = "SELECT * FROM mt_speaker_schema WHERE hcp_req IN :replacedHcpIdList", nativeQuery = true)
    List<MtSpeakerSchema> findVendorAccountDataList(@Param("replacedHcpIdList") List<String> replacedHcpIdList);

    @Query(value = "SELECT * FROM mt_speaker_schema mss WHERE DATE(DATE_ADD(mss.migration_last_use_date, INTERVAL 12 MONTH)) >= CURDATE() AND mss.id NOT IN (:notInSpeakerIdList)", nativeQuery = true)
    List<MtSpeakerSchema> findAllByMigrationLastUseDateNotBefore(@Param("notInSpeakerIdList") List<String> notInSpeakerIdList);

    @Query(value = "SELECT * FROM mt_speaker_schema mss WHERE DATE(DATE_ADD(mss.migration_last_use_date, INTERVAL 12 MONTH)) >= CURDATE()", nativeQuery = true)
    List<Object[]> findAllByMigrationLastUseDateNotBefore();

    @Query(value = "SELECT distinct id FROM mt_speaker_schema mss WHERE DATE(DATE_ADD(mss.migration_last_use_date, INTERVAL 12 MONTH)) >= CURDATE()", nativeQuery = true)
    List<String> findIdsForSpeakerLockTask();

    // centris 迁移过来的数据， 根据创建时间过滤
    @Query(value = "SELECT distinct id FROM mt_speaker_schema mss WHERE DATE(DATE_ADD(mss.migration_create_date, INTERVAL 12 MONTH)) >= CURDATE()", nativeQuery = true)
    List<String> findIdsForMigrationCreateTimeSpeakerLockTask();
    // ievent 创建的讲者， 没有centris create time 的表示ievent 创建的讲者
    @Query(value = "SELECT distinct id FROM mt_speaker_schema mss WHERE mss.migration_create_date is null and DATE(DATE_ADD(mss.create_time, INTERVAL 12 MONTH)) >= CURDATE()", nativeQuery = true)
    List<String> findIdsCreateTimeForSpeakerLockTask();

    @Query(value = "select distinct mss.id from mt_speaker_schema mss inner join mt_speaker_lockout ms on mss.hcp_id = ms.speaker_code where mss.lock_out = 1 and ms.reason_type = 'Not invited in 12 months' and ms.begin_time <= curdate() and ms.end_time >= curdate() and ms.working = 1", nativeQuery = true)
    List<String> findLockedIdsForSpeakerLockTask();

    Page<MtSpeakerSchema> findAllByIdNotIn(Collection<String> idNotInList, Pageable pageable);

    MtSpeakerSchema findByHcpId(String hcpId);

    List<MtSpeakerSchema> findAllByAccId(String accId);

    MtSpeakerSchema findByAccId(String accId);
}
